/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db.JDBC;

import core.School;
import core.Program;
import core.Course;
import db.interfaces.SchoolDAO;
import java.sql.Connection;
import java.util.Collection;
/**
 *
 * @author Ahlberg
 * A class for fetching data from the schhol database.
 */
public class SchoolJdbcDAO extends BaseJdbcDAO implements SchoolDAO
{
    /**
     * 
     * @param con
     */
    public SchoolJdbcDAO(Connection con)
    {
        super(con);
    }

    /**
     * 
     * @param id
     * @return
     */
    @Override
    public School getSchoolById(long id)
    {
        return super.getSchoolById(id);
    }
    /**
     * 
     * @param beginsWith
     * @return
     */
    public Collection<School> searchForSchoolsByName(String beginsWith)
    {
        return search(beginsWith, "name");
    }
    /**
     * 
     * @param beginsWith
     * @param fieldName
     * @param asc
     * @return
     */
    public Collection<School> searchForSchoolsByParameters(String beginsWith, String fieldName, boolean asc)
    {
        String sql = "";
        String sort = "ASC";
        if(!asc)
            sort = "DESC";
        if(fieldName.equals(""))
            sql = "SELECT * FROM Schools ORDER BY " + fieldName + " " + sort +";";
        else
            sql = "SELECT * FROM Schools WHERE " +
                "id LIKE '" + beginsWith + "%' OR " +
                "name LIKE '" + beginsWith + "%' ORDER BY " +
                fieldName + " " + sort +";";
        return createSchoolCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param beginsWith
     * @param field
     * @return
     */
    private Collection<School> search(String beginsWith, String field)
    {
        String sql = "SELECT * FROM Schools WHERE " + field + " LIKE '" + beginsWith + "%' ORDER BY " + field + " ASC;";
        return createSchoolCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param name
     * @return
     */
    public Collection<School> getSchoolsByName(String name)
    {
        if(name == null)
            return null;
        String sql = "SELECT * FROM Schools WHERE name='" + name + " ORDER BY name ASC';";
        return createSchoolCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @return
     */
    public Collection<School> getAllSchools()
    {
        String sql = "SELECT * FROM Schools ORDER BY name ASC;";
        return createSchoolCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param school
     * @param program
     * @return
     */
    public boolean addProgramToSchool(School school, Program program)
    {
        if(school == null || program == null)
            return false;
        String sql = "INSERT INTO SchoolProgram (school, program) " +
                "VALUES ("
                + "'" + school.getId() + "', "
                + "'" + program.getId() + "');";
        return add(sql);
    }
    /**
     * 
     * @param school
     * @param program
     * @return
     */
    public boolean deleteProgramFromSchool(School school, Program program)
    {
        if(school == null || program == null)
            return false;
        String sql = "DELETE FROM SchoolProgram WHERE school = '" + school.getId() + "' AND program = '" + program.getId() + "';";
        return delete(sql);
    }
    /**
     * 
     * @param school
     * @param course
     * @return
     */
    public boolean addCourseToSchool(School school, Course course)
    {
        if(school == null || course == null)
            return false;
        String sql = "INSERT INTO SchoolCourse (school, course) " +
                "VALUES ("
                + "'" + school.getId() + "', "
                + "'" + course.getId() + "');";
        return add(sql);
    }
    /**
     * 
     * @param school
     * @param course
     * @return
     */
    public boolean deleteCourseFromSchool(School school, Course course)
    {
        if(school == null || course == null)
            return false;
        String sql = "DELETE FROM SchoolCourse WHERE school = '" + school.getId() + "' AND course = '" + course.getId() + "';";
        return delete(sql);
    }
    /**
     * 
     * @param school
     * @return
     */
    public boolean containsSchool(School school)
    {
        if(school == null)
            return false;
        String sql = "SELECT * FROM Schools WHERE id='" + school.getId() + "';";
        return contains(sql);
    }
    /**
     * 
     * @param school
     * @return
     */
    public boolean addSchool(School school)
    {
        if(school == null)
            return false;
        String sql = "INSERT INTO Schools (name, description, weblink, XMLSource) " +
                           "VALUES ("
                           + "'" + school.getName() + "', "
                           + "'" + school.getDescription() + "', "
                           + "'" + school.getWebLink() + "', "
                           + "'" + school.getXMLSource() + "');";
        boolean b = add(sql);
        school.setId(getLatestAddedId("Schools"));
        return b;
    }
    /**
     * 
     * @param school
     * @return
     */
    public boolean editSchool(School school)
    {
        if(school == null)
            return false;
        String sql = "UPDATE Schools SET  " +
                "name='" + school.getName() +
                "', description = '" + school.getDescription() +
                "', weblink = '" + school.getWebLink() +
                "', XMLSource = '" + school.getXMLSource() +
                "' WHERE id='" + school.getId() + "';";
        return edit(sql);
    }
    /**
     * 
     * @param school
     * @return
     */
    public boolean deleteSchool(School school)
    {
        if(school == null)
            return false;
        deleteSchoolFromAllBindings(school);
        String sql = "DELETE FROM Schools WHERE id = ' " + school.getId() + "';";
        return delete(sql);
    }
    /**
     * 
     * @param school
     */
    private void deleteSchoolFromAllBindings(School school)
    {
        String sql = "DELETE FROM SchoolProgram WHERE school = ' " + school.getId() + "'";
        delete(sql);
        sql = "DELETE FROM SchoolCourse WHERE school = ' " + school.getId() + "'";
        delete(sql);
    }
}
